﻿using System.Data;
namespace WPFExcelReport
{
    public partial class ITXEXRPT
    {  
        private void ABCOMPARE()
        {
            string CommdandInsert = "";
            foreach (DataTable tb in dsSource.Tables)
            {
               
                int rows = tb.Rows.Count;
                if (rows == 0)
                    continue;
                string tbName = tb.TableName;
                exAgent.SetCurrentSheet(Standard);
                exAgent.CopySheet(tbName);
                string update = "or1.CommandText = "+'"'+"UPDATE "+tbName+" SET "; 
                string paramaters = "";
                string insertf = " or1.CommandText="+'"'+"INSERT INTO "+tbName+" ( ";
                string insertv = "+" + '"' + " VALUES (";
                    string insertv2 ="+"+'"'+ " VALUES (";
                int xcm = 8;
                DataRow row = tb.Rows[0];
                foreach (DataColumn cm in tb.Columns)
                {
                    update+=cm.ColumnName+"=:"+cm.ColumnName+":,";
                    paramaters += "or1.Parameters.Add("+'"'+cm.ColumnName+'"'+", OracleType.NVarChar).Value = dwData["+'"'+cm.ColumnName+'"'+"].ToString();" ;
                    insertf += cm.ColumnName + ",";
                    insertv += "'" + row[cm.ColumnName].ToString() + "',";
                    insertv2 += ":"+cm.ColumnName+":,";;
                    exAgent.SetCellValue(xcm, 1,"dwReturn["+'"'+cm.ColumnName+'"'+"]=" +'"');
                    exAgent.SetCellValue(xcm, rows+2, '"'+";");
                    //exAgent.SetCellValue(xcm, rows + 3, cm.ColumnMapping); 
                    xcm++;
                }

                int count = tb.Columns.Count;
                foreach(DataRow dwData in tb.Rows)
                {
                    string value = "INTO "+ tbName+"  VALUES (";
                   for(int i=0;i<count;i++)
                    {
                        value +="'"+ dwData[i].ToString()+"',";
                    }
                   
                    value += ")";
                    value=value.Replace(",)", ")")+System.Environment.NewLine;
                    CommdandInsert += value;
                }

                update += "***";
                update = update.Replace(",***", ";");
                insertf += "***";
                insertf = insertf.Replace(",***", ")"+'"');
                insertv += "***";
                insertv = insertv.Replace(",***", ")"+'"'+";");
                insertv2 += "***";
                insertv2 = insertv2.Replace(",***", ")"+'"'+";");
                exAgent.SetCellValue(1, 5, update);
                exAgent.SetCellValue(2, 5, " or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);");
                exAgent.SetCellValue(3, 5, insertf);
                exAgent.SetCellValue(4, 5, insertv2); 
                exAgent.SetCellValue(5, 5, " or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);"); 
                exAgent.DataTableToExcel(tb, 8, 2, true); 
            }
            CommdandInsert = "INSERT ALL " + CommdandInsert + " SELECT* FROM DUAL";
            exAgent.SetCellValue(6, 5, CommdandInsert);
            exAgent.SetCurrentSheet(Standard);
            exAgent.DataTableToExcel(dsSource.Tables[0],3,1);
        }
    }
}
